1. Field of the Invention
The present application relates generally to an improved data processing system and in particular to a method and apparatus for managing a database. Still more particularly, the present application relates to a computer implemented method, apparatus, system, and computer usable program product for representing a group hierarchy structure in a relational database that supports both static and dynamic groups.
2. Description of the Related Art
A relational database is a database in which data is stored in one or more related database tables. Each database table includes columns and rows. A column, referred to herein as a field, is a predefined data category. For example, a database table having information for the residents of a municipality could include fields for the following categories of information: name, address, and phone number.
Each row of a database table, referred to herein as a record, contains a unique instance of data for the category defined by the column. In the example above, a record in the database table for the municipality could include the following information: Joe Smith, 111 Main Street, (555) 555-5555. The record includes all the data in a given row of the database table corresponding to one or more predefined categories in the database table.
A single relational database system can be spread across several database tables. A current relational database can include anywhere from one to more than one-thousand database tables. For example, the relational database for the municipality could include another table for municipal parking fines. The table for parking fines could include fields for name, amount owed, and due date.
A database manager is software to manage the collection of data stored in a set of one or more database tables. The database manager can retrieve data from tables based on relations of the data. Relations are a way of organizing data to permit flexible and varied operations on data. In other words, data can be accessed or reassembled in many different ways in response to a user query without having to reorganize the data in the set of one or more relational database tables. A database manager typically permits a user to sort data in the relational database based on any field and/or generate results that include only certain fields from one or more records.
In the example above, a user might query the municipality relational database for a mailing list of all residents that received parking tickets. The relational database could provide query results including the names of residents from the name field of the parking fines table and addresses from the table for resident information.
Relational databases are frequently implemented using groups of objects. A group of objects includes any collection of objects. Typically, groups of objects in a relational database are implemented using a group table and a membership table. As used herein, a group table is a table of all the groups defined in the relational database. The group table contains information about the group, such as the group's name and/or the group's primary key. A membership table is a table including information about each member of every group in the group table. Each member of a group has a record in a membership table.
Thus, if the municipality has two-hundred and five residents that are all members of the group “Residents”, the group table would include one record for the “Residents” group. The membership table would include two-hundred and five records for the “Residents” group in which each record would be marked with the “Residents” group primary key.
However, users frequently need to utilize nested groups. A nested group is a group member that is itself a group having members. For example, a group “A” can include members 1, 2, and 3. Member 3 can itself be a group having members B1 and B2. When nested groups are members of other groups in this manner, the members of the nested groups are considered to be indirect members of the containing groups. In other words, members B1 and B2 in this example are considered members of group “A.”
To determine group membership in such a case, the database manager must perform multiple queries to work through the entire hierarchy of nested groups. In the above example, the results of the first query, providing members 1, 2, and 3, need to be examined to determine if any member is a nested group. For each member that is a group, such as member 3, another query must be executed to determine the nested group's membership. This process continues recursively until no more members that are groups are found. This system of determining group membership where hierarchies of nested groups are utilized is inefficient.
Moreover, group implementation relying on defining group members in a membership table means the membership of a group must be all static members. A static member is a member of a group that must be manually added to the group or deleted from the group in the membership table in order to change the group membership. This requirement creates a lot of maintenance for users.
An alternative group implementation is one in which the group members are defined by a set of common attributes, not by records in a membership table. These are dynamic groups because they require no membership list maintenance. If an object in the database has attributes that match those required for dynamic group membership, the object is automatically part of the dynamic group.
Users need both static and dynamic groups to solve business problems. However, current relational database systems do not support utilization of both static and dynamic groups combined together in a single group hierarchy structure. In addition, multiple queries are typically required to identify the members of groups in a relational database system utilizing nested groups as the members of groups.